package sql;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Vector;

public class SuppliersOrdersDB {

	public ArrayList<String> getSupOrders() {

		ArrayList<String> arr=new ArrayList<String>();
		
			Statement stmt;
			SQLConnection conn = new  SQLConnection();
			try {
				stmt = conn.getConnection().createStatement();
			
			
			ResultSet rs= stmt.executeQuery("SELECT mtl.supplier_orders.order_id,mtl.supplier.name,mtl.supplier_orders.start_date, mtl.supplier_orders.end_date,mtl.supplier_orders.price,mtl.supplier_orders.status FROM mtl.supplier_orders,mtl.supplier where mtl.supplier_orders.supp_id=mtl.supplier.sup_id;");
				while (rs.next())
				{
					
					arr.add(rs.getString(1));
					arr.add(rs.getString(2));
					arr.add(rs.getString(3));
					arr.add(rs.getString(4));
					arr.add(rs.getString(5));
					arr.add(rs.getString(6));
				}
		
			}
			catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	return arr;
}

	public int openSupOrderDB(ArrayList<String> supOrderO, Vector<Vector<String>> dataSuppliersOrder) {
		try 
		{
			
			Vector<String> vect = new Vector<String>();
			Statement stmt;
			SQLConnection conn = new  SQLConnection();
			stmt = conn.getConnection().createStatement();
			System.out.println("is ?:"+supOrderO.get(0)+"");

			ResultSet rs= stmt.executeQuery("SELECT mtl.supplier.sup_id FROM mtl.supplier WHERE mtl.supplier.name='"+supOrderO.get(0)+"';");
			rs.next();
			String suppid=rs.getString(1);
			java.util.Date dt = new java.util.Date();
			java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd");
			String currentTime = sdf.format(dt);
		 	stmt.executeUpdate("insert into mtl.supplier_orders(supp_id,start_date,price,status) values('"+Integer.parseInt(suppid)+"','"+currentTime+"','"+supOrderO.get(1)+"','"+"Initialize"+"');");
		 	ResultSet rs2= stmt.executeQuery("SELECT order_id FROM mtl.supplier_orders WHERE order_id = (SELECT MAX(order_id) FROM mtl.supplier_orders);");
			rs2.next();
			String orderid=rs2.getString(1);
		 	int ind=0;
		 	while(ind<dataSuppliersOrder.size())
		 	{
		 	vect=dataSuppliersOrder.elementAt(ind);
		 	System.out.print(vect.get(1));System.out.print(vect.get(2));System.out.println(vect.get(3));
		 	String[] parts = vect.get(1).split("-");
		 	String part1 = parts[0]; 
		 	stmt.executeUpdate("insert into mtl.items_in_sup_order(order_id,iditems,quantity,price) values('"+Integer.parseInt(orderid)+"','"+Integer.parseInt(part1)+"','"+vect.get(2)+"','"+vect.get(3)+"');");
		 	ind++;
		 	
		 	}
			return 1;
		
		}
  
		catch (Exception ex )
		{
				ex.printStackTrace();
		}
		return 0;
		
	}
	public ArrayList<String> watchorder(String id) {//retrive order items by id

		ArrayList<String> arr=new ArrayList<String>();
		
			Statement stmt;
			SQLConnection conn = new  SQLConnection();
			try {
				stmt = conn.getConnection().createStatement();
			
			
			ResultSet rs= stmt.executeQuery("SELECT c.name,d.name,b.quantity,b.price FROM mtl.supplier_orders a ,mtl.items_in_sup_order b , mtl.supplier c , mtl.items d WHERE a.order_id = '"+id+"' AND a.order_id = b.order_id AND a.supp_id = c.sup_id AND d.iditems = b.iditems ;");
				while (rs.next())
				{
					
					arr.add(rs.getString(1));
					arr.add(rs.getString(2));
					arr.add(rs.getString(3));
					arr.add(rs.getString(4));
					
				}
		
			}
			catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	return arr;
}
	public int updateorderstatusdb(ArrayList<String> ar,String permtion) {//retrive order items by id

		
			String status;
			Statement stmt,stmt1,stmt2,stmt0;
			SQLConnection conn = new  SQLConnection();
			try {
					stmt = conn.getConnection().createStatement();
					stmt0 = conn.getConnection().createStatement();
					ResultSet rs2=stmt0.executeQuery("select status from mtl.supplier_orders where order_id = '"+ar.get(0)+"'");
					rs2.next();
					status=rs2.getString(1);
						if((permtion.equals("2")||permtion.equals("2")) && status.equals("Initialize"))
						{
							return 3;
						}
						else
						{
							java.util.Date dt = new java.util.Date();
							java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd");
							String currentTime = sdf.format(dt);
							stmt.executeUpdate("update mtl.supplier_orders set status = '"+ar.get(1)+"', end_date= '"+currentTime+"' where order_id = '"+ar.get(0)+"';");
							
							if(ar.get(1).equals("order in warehouse"))//insert item to inventory
							{
								stmt1=conn.getConnection().createStatement();
								ResultSet rs1 = stmt1.executeQuery("SELECT iditems,quantity,status FROM mtl.items_in_sup_order a,supplier_orders b WHERE a.order_id ='"+ ar.get(0)+"' AND a.order_id = b.order_id");
								while(rs1.next())
								{
									
										stmt2=conn.getConnection().createStatement();
										stmt2.executeUpdate("insert into mtl.inventory(id,quantity) values('"+rs1.getInt(1)+"','"+rs1.getInt(2)+"')");
										stmt2.close();
									
								}
									
								
							}
						}
				return 1 ;
			
			
			
			}
	
			catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	return 0 ;
}
}
